package com.demo.data;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

public class DatabseHandler 
{
	private Connection conn;
	private Statement statement;
	private String url, user, password;
	private int id;
	private String name;
	private String category;
	private String year;
	private String country;
	private String length;
	
	public DatabseHandler() 
	{
		
	}
	
	public void init() 
	{
		Properties properties = new Properties();
		try 
		{
			properties.load(new FileInputStream("database.properties"));
			url = properties.getProperty("url");
			user = properties.getProperty("user");
			password = properties.getProperty("password");
			System.out.println("Kết nối cơ sỡ dữ liệu");
			System.out.println(url);
			System.out.println(user + " " + password);
		}
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}
	
	public void DBConnect() 
	{
		try
		{			
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("DbHandle:  Kết nối csdl thành công");
			statement = conn.createStatement();
		}
		catch(ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IllegalAccessException e) 
		{
			e.printStackTrace();
		} 
		catch (InstantiationException e) 
		{
			e.printStackTrace();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
	}
	
	public DatabaseMetaData getMetaData() throws SQLException 
	{
		return conn.getMetaData();
	}

	public ResultSet executeQuery(String sql) throws SQLException 
	{
		return statement.executeQuery(sql);
	}

	public ArrayList<Film> getData() 
	{
		String sql="SELECT * FROM Film";
		ArrayList<Film> listFilm = new ArrayList<>();
		try
		{
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next())
			{ 				
				int id = rs.getInt("Id");
				String name = rs.getString("Name");
				String category = rs.getString("Category");
				String year = rs.getString("Year");
				String country = rs.getString("Country");
				String length = rs.getString("Length");
				Film film = new Film(id, name, country, category, year, length);
				listFilm.add(film);
			}
			
			return listFilm;
		
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}		

		return null;
	}
	
	public int intsertData(Film film)
	{
		initData(film);
		String sql = "INSERT INTO Film(Id,Name,Country,Category, Year, Length) VALUES('"+ id + "', '" + name + "', '" + country + "', '" 
						+ category + "', '" + year + "', '" + length + "')";

		try
		{
			return statement.executeUpdate(sql);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			return -1;
		}
	}
	
	public int updateData(Film film)
	{
		initData(film);
		String sql = "UPDATE Film SET Id = '" + id + "', Name = '" + name + "', Country = '" + country + 
				"', Category = '" + category + "', Year = '" +  year + "', Length = '" + length + "')";		
		try
		{
			return statement.executeUpdate(sql);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			return -1;
		}

	}
	
	public int deleteData(Film film)
	{
		initData(film);
		String sql = "UPDATE Film SET Id = '" + id + "', Name = '" + name + "', Country = '" + country + 
				"', Category = '" + category + "', Year = '" +  year + "', Length = '" + length + "')";		
		try
		{
			return statement.executeUpdate(sql);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			return -1;
		}

	}
	
	public int login(String username, String password) 
	{
		String sql="SELECT * FROM User";		
		try
		{
			ResultSet rs = statement.executeQuery(sql);
			if (rs.next())
			{ 				
				return 1;
			}		
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}	
		return -1;
	}
	
	public void initData(Film film) 
	{
		id = film.getId();
		name = film.getName();
		category = film.getCategory();
		year = film.getYear();
		country = film.getCountry();
		length = film.getLength();
	}
	
}
